﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace _14_DateGridView
{
    public partial class Form1 : Form
    {
        BindingList<User> users = new BindingList<User>();

        public Form1()
        {
            InitializeComponent();
            //不允许用户调整表格的高度
            this.dataGridView1.AllowUserToResizeRows = false;
            //不允许用户调整表格的宽度
            this.dataGridView1.AllowUserToResizeColumns = false;

            //绑定数据源
            this.dataGridView1.DataSource = users;
        }
        /// <summary>
        /// 查询命令
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            users.Clear();//清空表格数据库，防止有数据

            //拼接SQL语句
            string sql = "SELECT * FROM UserTable";
            //调用数据库帮助类的方法，执行查询
            using (SqlDataReader sqlDate = SQLHelper.GetSqlDataReader(sql))
            {
                //读取获取到的数据内容
                while (sqlDate.Read())
                {
                    User user = new User();
                    user.ID =sqlDate["userID"].ToString();
                    user.Name = sqlDate["userName"].ToString();
                    user.PassWord = sqlDate["userPassWord"].ToString();
                    user.UserPower = sqlDate["userPower"].ToString();
                    //将获取到的数据添加到数据源中
                    users.Add(user);
                }

            }
        }
        /// <summary>
        /// 增加用户
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            //获取界面输入的信息
            string id  = this.textBox1.Text;
            string name = this.textBox2.Text;
            string password = this.textBox3.Text;
            string power = this.textBox4.Text;

            //拼接SQL语句：INSERT INTO UserTable(userName,userPassWord,userPower) VALUES('user2','123456','用户')
            string sql = string.Format("INSERT INTO UserTable(userName,userPassWord,userPower) VALUES('{0}','{1}','{2}')",name,password,power);
            //调用数据库帮助类的方法，执行插入操作
            int result = SQLHelper.Update(sql);//返回受影响的行数
            if (result > 0)
            {
                //更新表格中的数据
                button1_Click(null,null);
            }
            else
            {
                //提示插入失败
                MessageBox.Show("插入失败");
            }

        }
        /// <summary>
        /// 点击单元格
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            //判断是否点击的是表头
            if (e.RowIndex < 0)
            {
                return;
            }

            //获取当前选中的行所对应的User对象
            User user = this.dataGridView1.Rows[e.RowIndex].DataBoundItem as User;
            //设置文本框的内容
            this.textBox1.Text = user.ID;
            this.textBox2.Text = user.Name;
            this.textBox3.Text = user.PassWord;
            this.textBox4.Text = user.UserPower;
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button3_Click(object sender, EventArgs e)
        {
            //获取界面输入的信息
            string id = this.textBox1.Text;
            string name = this.textBox2.Text;
            string password = this.textBox3.Text;
            string power = this.textBox4.Text;


            //拼接修改SQL语句:UPDATE UserTable SET userName='user22',userPassWord='654321',userPower='用户' Where userID=4
            string sql = string.Format("UPDATE UserTable SET userName='{0}',userPassWord='{1}',userPower='{2}' Where userID={3}", name, password, power,id);

            //向数据库执行修改操作
            int result = SQLHelper.Update(sql);
            if (result > 0)
            {
                //更新表格中的数据
                button1_Click(null, null);
            }
            else
            {
                //提示修改失败
                MessageBox.Show("修改失败");
            }

        }

        private void button4_Click(object sender, EventArgs e)
        {
            //获取界面输入的信息
            string id = this.textBox1.Text;

            //拼接删除SQL语句:DELETE FROM UserTable WHERE userID=3
            string sql = string.Format("DELETE FROM UserTable WHERE userID={0}", id);

            //向数据库执行删除操作
            int result = SQLHelper.Update(sql);
            if (result > 0)
            {
                //更新表格中的数据
                button1_Click(null, null);
            }
            else
            {
                //提示删除失败
                MessageBox.Show("删除失败");
            }
        }
    }
}
